package com.jinxin.platform.cdfaceacs.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.util.StringUtils;
import sun.misc.BASE64Decoder;

import java.util.List;

/**
 * All rights Reserved, Designed By www.beonelot.com
 *
 * @version: V1.0
 * @Copyright: www.beonelot.com Inc. All rights reserved.
 * @Description：
 * @Date: 2019-11-19
 * @Author: Lingnan
 **/
public class ExportExcelUtils {
    /**
     * @Title: exportExcel
     * @Description: 导出Excel的方法
     * @author: evan @ 2014-01-09
     * @param workbook
     * @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet)
     * @param sheetTitle  （sheet的名称）
     * @param headers    （表格的标题）
     * @param result   （表格的数据）
     * @throws Exception
     */
    public static void exportExcel(HSSFWorkbook workbook, int sheetNum,
                            String sheetTitle, String[] headers, List<List<String>> result) throws Exception {
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);
        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 指定当单元格内容显示不下时自动换行
        style.setWrapText(true);

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text.toString());
        }
        // 遍历集合数据，产生数据行
        if (result != null) {
            for(int i = 0; i < result.size(); i++) {
                row = sheet.createRow(i + 1);
                for (int j = 0; j < result.get(i).size(); j++) {
                    HSSFCell cell = row.createCell(j);
                    String value = result.get(i).get(j);
                    if(!StringUtils.isEmpty(value) && value.startsWith("data:image/")){
                        // 利用HSSFPatriarch将图片写入EXCEL
                        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                        /**
                         * 该构造函数有8个参数
                         * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
                         * 后四个参数，前两个表示图片左上角所在的cellNum和 rowNum，后天个参数对应的表示图片右下角所在的cellNum和 rowNum，
                         * excel中的cellNum和rowNum的index都是从0开始的
                         *
                         */
                        //图片一导出到单元格M列中
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
                                (short) 12, (i+1), (short) 13, (i+2));
                        // 插入图片
                        BASE64Decoder decoder = new BASE64Decoder();
                        byte[] bytes = decoder.decodeBuffer(value.split(",")[1]);
                        patriarch.createPicture(anchor, workbook.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }else {
                        cell.setCellValue(value);
                    }
                }
            }
        }
    }

    public void exportExcelHori(HSSFWorkbook workbook, int sheetNum,
                                String sheetTitle, String[] headers, List<List<String>> result) throws Exception {
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);
        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);

        // 指定当单元格内容显示不下时自动换行
        style.setWrapText(true);

        // 产生表格标题行
        List<String> list = result.get(0);
        for (int i = 0; i < headers.length; i++) {
            HSSFRow row = sheet.createRow(i);
            // 第一列是表头
            HSSFCell cell = row.createCell(0);
            HSSFCell cell2 = row.createCell(1);// 第二列是数据
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text.toString());
            // 数据
            String str = list.get(i);
            if(!StringUtils.isEmpty(str)&&str.startsWith("data:image/")){
                // 利用HSSFPatriarch将图片写入EXCEL
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                /**
                 * 该构造函数有8个参数
                 * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
                 * 后四个参数，前两个表示图片左上角所在的cellNum和 rowNum，后天个参数对应的表示图片右下角所在的cellNum和 rowNum，
                 * excel中的cellNum和rowNum的index都是从0开始的
                 *
                 */
                //图片一导出到单元格B7中
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
                        (short) 1, i, (short) 2, (i+1));
                // 插入图片
                BASE64Decoder decoder = new BASE64Decoder();
                byte[] bytes = decoder.decodeBuffer(str.split(",")[1]);
                patriarch.createPicture(anchor, workbook.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG));
            }else{
                String cellStr = !StringUtils.isEmpty(str) ? str.trim() : "空";
                cell2.setCellValue(cellStr);
            }
        }

    }

}
